package com.ttl.common.db;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;

import com.ttl.common.util.CommonUtils;

public class DBUtil {

    private static int dayStr(String str) {
        int i;
        for(i=0;i<str.length();i++)
          if(str.substring(i, i+1).equals("/"))
            break;      
        
        return Integer.parseInt(str.substring(0,i));
      }
    
      private static int monthStr(String str) {
        int begin=0,end=0;
        for(int i=0;i<str.length();i++)
          if(str.substring(i, i+1).equals("/"))
          	if (begin==0)
          		begin=i;
          	else
          		end =i;          		
        
        return Integer.parseInt(str.substring(begin+1,end));
      }              
      
      private static int yearStr(String str) {
        return Integer.parseInt(str.substring(str.length()-4, str.length() ));
      }
      
      private static long daysDiff(String start, String end ) {

        Calendar calendar1 = Calendar.getInstance();
        Calendar calendar2 = Calendar.getInstance();
        calendar1.set(yearStr(start), monthStr(start), dayStr(start));
        calendar2.set(yearStr(end), monthStr(end), dayStr(end));
        long milliseconds1 = calendar1.getTimeInMillis();
        long milliseconds2 = calendar2.getTimeInMillis();
        long diff = milliseconds2 - milliseconds1;
        long diffDays = diff / (24 * 60 * 60 * 1000);      
        
        return diffDays;
      }	
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public void insert(String start, String end,
			String remainMoney, String ttl) {
		
		  double balance = 0;
		  if(CommonUtils.isNumber(remainMoney)) {
			  balance = Double.parseDouble(remainMoney);
		  }
		
		  ArrayList payDate=null;
	      ArrayList dayReport = new ArrayList();                        
	      DateUtil today = new DateUtil(monthStr(start), dayStr(start), yearStr(start) );
	      long diffDays = daysDiff(start, end);
	                               
	      try {
	        DBConnect db = new DBConnect();
	        db.connectDB();                        
	        Statement stmt = db.conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
	        stmt.execute("delete from TTL_PAY_SUMMARY where ttl like '%"+ttl+"%'");            
	        
	        int ii=0;
	        do{
	          dayReport.add(today);          
	          String sql = "insert into TTL_PAY_SUMMARY(docDate, ttl) values('"+today+"', '"+ttl+"')";
	          stmt.execute(sql);
	          today = today.next();
	        }while (ii++ < diffDays );
	            
	        payDate= new DBUtil().select(start, end, ttl);
	        String sqlStr=null;
	        //double bal=100000;
	        for(int j=0; j<dayReport.size(); j++) {
	          boolean foundPayDateTotal=false;
	          for(int i=0; i<payDate.size();i++){
	            //if(dayReport.get(j).toString().equals(    ((ReportSummary)payDate.get(i)).getReportDate().toString()  )  ) {
	            String dayReportStr = dayReport.get(j).toString();//m/d/yyyy
	            String payDateStr = ((ReportSummary)payDate.get(i)).getReportDate().toString();
	            payDateStr = Integer.parseInt(payDateStr.substring(5, 7))+"/"+Integer.parseInt(payDateStr.substring(8, 10))+"/"+payDateStr.substring(0, 4);
	            if(   dayReportStr.equals(payDateStr))   {                                                    
	              foundPayDateTotal=true;
	              balance = balance-((ReportSummary)payDate.get(i)).getTotal()+((ReportSummary)payDate.get(i)).getN100()+((ReportSummary)payDate.get(i)).getN108();
	              sqlStr="update TTL_PAY_SUMMARY set "+                      
	                "n104="+((ReportSummary)payDate.get(i)).getN104()+","+"n108="+((ReportSummary)payDate.get(i)).getN108()+","+
	                "n302="+((ReportSummary)payDate.get(i)).getN302()+","+"n304="+((ReportSummary)payDate.get(i)).getN304()+","+
	                "n306="+((ReportSummary)payDate.get(i)).getN306()+","+"n308="+((ReportSummary)payDate.get(i)).getN308()+","+
	                "n309="+((ReportSummary)payDate.get(i)).getN309()+","+"n310="+((ReportSummary)payDate.get(i)).getN310()+","+
	                "n311="+((ReportSummary)payDate.get(i)).getN311()+","+"n313="+((ReportSummary)payDate.get(i)).getN313()+","+
	                "n315="+((ReportSummary)payDate.get(i)).getN315()+","+"n317="+((ReportSummary)payDate.get(i)).getN317()+","+
	                "n318="+((ReportSummary)payDate.get(i)).getN318()+","+"n319="+((ReportSummary)payDate.get(i)).getN319()+","+
	                "n320="+((ReportSummary)payDate.get(i)).getN320()+","+"n401="+((ReportSummary)payDate.get(i)).getN401()+","+

	                "n305="+((ReportSummary)payDate.get(i)).getN305()+","+"n307="+((ReportSummary)payDate.get(i)).getN307()+","+
	                "n312="+((ReportSummary)payDate.get(i)).getN312()+","+"n314="+((ReportSummary)payDate.get(i)).getN314()+","+
	                "n316="+((ReportSummary)payDate.get(i)).getN316()+","+
	                "n100="+((ReportSummary)payDate.get(i)).getN100()+","+
	                "total="+((ReportSummary)payDate.get(i)).getTotal()+","+"balance="+balance+
	                " where docDate='"+((ReportSummary)payDate.get(i)).getReportDate()+"' and ttl='"+ttl+"'";
	            }            
	          }
	          if (foundPayDateTotal==false) {
	              sqlStr = "update TTL_PAY_SUMMARY set balance="+balance+" where docDate='"+dayReport.get(j).toString()+"' and ttl='"+ttl+"'";
	          }
	          stmt.execute(sqlStr);    
	        }        
	        db.closeDB();            
	      }catch(Exception e) { System.out.println(e.getMessage()); }		
	}
	
    @SuppressWarnings({ "unchecked", "rawtypes" })
	private ArrayList select(String start, String end, String ttl) {
    	
        start = yearStr(start)+"-"+monthStr(start)+"-"+dayStr(start);
        end = yearStr(end)+"-"+monthStr(end)+"-"+dayStr(end);
        String sql =null;
        if (ttl.equals("TTL-1")) {
          sql = "SELECT docDate, sum(n104)as n104, sum(n108)as n108, sum(n302)as n302, " +
                "sum(n304)as n304, sum(n306)as n306, sum(n308)as n308, sum(n309)as n309, " +
                "sum(n310)as n310, sum(n311)as n311, sum(n313)as n313, sum(n315)as n315, " +
                "sum(n317)as n317, sum(n318)as n318, sum(n319)as n319, sum(n320)as n320, " +
                "sum(n401)as n401, sum(total)as total, "+
                "sum(n305)as n305, sum(n307)as n307, sum(n312)as n312, sum(n314)as n314, sum(n316)as n316, sum(n100)as n100 " +
                "FROM VIEW_PAY_ONLINE "+
                "WHERE (docNo like 'CA1%' or docNo like 'OR-F1%') ";
        }else {
          sql = "SELECT docDate, sum(n104)as n104, sum(n108)as n108, sum(n302)as n302, " +
                "sum(n304)as n304, sum(n306)as n306, 0 as n308, sum(n309)as n309, " +
                "sum(n310)as n310, sum(n311)as n311, sum(n313)as n313, sum(n315)as n315, " +
                "sum(n317)as n317, sum(n318)as n318, sum(n319)as n319, sum(n320)as n320, " +
                "sum(n401)as n401, sum(total)-sum(n308) as total, "+
                "sum(n305)as n305, sum(n307)as n307, sum(n312)as n312, sum(n314)as n314, sum(n316)as n316, sum(n100)as n100 " +                
                "FROM VIEW_PAY_ONLINE "+
                "WHERE (docNo like 'CA2%' or docNo like 'OR-F2%')";
        }
        sql = sql+" and docDate between '"+start+"' and '"+end+"' Group by docDate order by docDate ";        
        System.out.println(sql);
        ArrayList resultData = new ArrayList();
        ReportSummary rpt = null;
        try {
            DBConnect db1 = new DBConnect();
            db1.connectDB();                                        
            ResultSet rs = db1.st.executeQuery(sql);
            while(rs.next()) {                
              rpt = new ReportSummary();
               
              rpt.setReportDate(rs.getDate("docDate")); 
              rpt.setN104(rs.getDouble("n104"));
              rpt.setN108(rs.getDouble("n108"));rpt.setN302(rs.getDouble("n302"));rpt.setN304(rs.getDouble("n304"));
              rpt.setN306(rs.getDouble("n306"));rpt.setN308(rs.getDouble("n308"));
              
              rpt.setN309(rs.getDouble("n309"));
              rpt.setN310(rs.getDouble("n310"));rpt.setN311(rs.getDouble("n311"));rpt.setN313(rs.getDouble("n313"));
              rpt.setN315(rs.getDouble("n315"));rpt.setN317(rs.getDouble("n317"));rpt.setN318(rs.getDouble("n318"));
              rpt.setN319(rs.getDouble("n319"));rpt.setN320(rs.getDouble("n320"));rpt.setN401(rs.getDouble("n401"));
              rpt.setTotal(rs.getDouble("Total"));
              
              rpt.setN305(rs.getDouble("n305"));rpt.setN307(rs.getDouble("n307"));rpt.setN312(rs.getDouble("n312"));
              rpt.setN314(rs.getDouble("n314"));rpt.setN316(rs.getDouble("n316"));rpt.setN100(rs.getDouble("n100"));
              
              resultData.add(rpt);
            }            
            db1.closeDB();            
        }catch(Exception e) { System.out.println("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"); }                
        return resultData;        
    }
}
